package com.fan.dao.provider;

import com.fan.dao.BaseDao;
import com.fan.pojo.Provider;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProviderDaoImpl implements ProviderDao{

    @Override
    public int getProviderCount(Connection connection,String proCode,String proName) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int count = 0;
        if (connection!=null){
            StringBuffer sql = new StringBuffer();
            List<Object> list = new ArrayList<>();
            if (!StringUtils.isNullOrEmpty(proCode)){
                sql.append("select count(1) as count from smbms_provider where proCode like ?");
                list.add("%"+proCode+"%");
                if (!StringUtils.isNullOrEmpty(proName)){
                    sql.append(" and proName like ?");
                    list.add("%"+proName+"%");
                }
            }else {
                if (!StringUtils.isNullOrEmpty(proName)){
                    sql.append("select count(1) as count from smbms_provider where proName like ?");
                    list.add("%"+proName+"%");
                }else {
                    sql.append("select count(1) as count from smbms_provider");
                }
            }
            Object[] params = list.toArray();
            System.out.println(sql);
            rs = BaseDao.execute(sql.toString(), params, connection, pstm,rs);
            if (rs.next()){
                count = rs.getInt("count");
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return count;
    }

    @Override
    public List<Provider> getProviderList(Connection connection, String proCode, String proName, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Provider> providerList = new ArrayList<>();
        if (connection != null){
            StringBuffer sql = new StringBuffer();
            List<Object> list = new ArrayList<>();
            if (!StringUtils.isNullOrEmpty(proCode)){
                sql.append("select * from smbms_provider where proCode like ?");
                list.add("%"+proCode+"%");
                if (!StringUtils.isNullOrEmpty(proName)){
                    sql.append(" and proName like ?");
                    list.add("%"+proName+"%");
                }
            }else {
                if (!StringUtils.isNullOrEmpty(proName)){
                    sql.append("select * from smbms_provider where proName like ?");
                    list.add("%"+proName+"%");
                }else {
                    sql.append("select * from smbms_provider");
                }
            }
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo = (currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            rs = BaseDao.execute(sql.toString(),params,connection,pstm,rs);
            while (rs.next()){
                Provider _provider = new Provider();
                _provider.setId(rs.getInt("id"));
                _provider.setProCode(rs.getString("proCode"));
                _provider.setProName(rs.getString("proName"));
                _provider.setProDesc(rs.getString("proDesc"));
                _provider.setProContact(rs.getString("proContact"));
                _provider.setProPhone(rs.getString("proPhone"));
                _provider.setProAddress(rs.getString("proAddress"));
                _provider.setProFax(rs.getString("proFax"));
                _provider.setCreatedBy(rs.getInt("createdBy"));
                _provider.setCreationDate(rs.getDate("creationDate"));
                _provider.setModifyDate(rs.getDate("modifyDate"));
                _provider.setModifyBy(rs.getInt("modifyBy"));
                providerList.add(_provider);
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return providerList;
    }

    @Override
    public int addProvider(Connection connection, Provider provider) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null){
            String sql = "insert into smbms_provider (proCode,proName,proDesc,proContact,proPhone,proAddress,proFax,creationDate,createdBy) values(?,?,?,?,?,?,?,?,?)";
            Object[] params = {provider.getProCode(),provider.getProName(),provider.getProDesc(),provider.getProContact(),provider.getProPhone(),provider.getProAddress(),provider.getProFax(),provider.getCreationDate(),provider.getCreatedBy()};
            updateRows = BaseDao.execute(sql,params,connection,pstm);
            BaseDao.closeResource(null,pstm,null);
        }
        return updateRows;
    }

    @Override
    public int deleteProviderById(Connection connection, Integer delId) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null){
            String sql = "delete from smbms_provider where id=?";
            Object[] params = {delId};
            updateRows = BaseDao.execute(sql, params, connection, pstm);
            BaseDao.closeResource(null,pstm,null);
        }
        return updateRows;
    }

    @Override
    public Provider getProviderById(Connection connection, String id) throws SQLException {
        Provider provider = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        if (connection != null){
            String sql = "select * from smbms_provider where id=?";
            Object[] params = {id};
            rs = BaseDao.execute(sql,params,connection,pstm,rs);
            if (rs.next()){
                provider = new Provider();
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                provider.setCreatedBy(rs.getInt("createdBy"));
                provider.setCreationDate(rs.getDate("creationDate"));
                provider.setModifyBy(rs.getInt("modifyBy"));
                provider.setModifyDate(rs.getDate("modifyDate"));
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return provider;
    }

    @Override
    public List<Provider> getProviderList(Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList<Provider> providerList = new ArrayList<>();
        if (connection!=null){
            String sql = "select id,proName from smbms_provider";
            Object[] params = {};
            rs = BaseDao.execute(sql,params,connection,pstm,rs);
            while (rs.next()){
                Provider _provider = new Provider();
                _provider.setId(rs.getInt("id"));
                _provider.setProName(rs.getString("proName"));
                providerList.add(_provider);
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        System.out.println("providerList(Dao):"+providerList);
        return providerList;
    }

    @Override
    public boolean updateProviderById(Connection connection, String id, String proCode, String proName, String proDesc, String proContact, String proPhone, String proAddress, String proFax) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null){
            String sql = "update smbms_provider set proCode=?,proName=?,proDesc=?,proContact=?,proPhone=?,proAddress=?,proFax=? where id = ?";
            Object[] params = {proCode,proName,proDesc,proContact,proPhone,proAddress,proFax,id};
            updateRows = BaseDao.execute(sql,params,connection,pstm);
            return true;
        }else {
            return false;
        }
    }




}
